package com.csvreader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class TestExits2 {

	public static void main(String[] args) throws Exception {  
        initData();  
    }  
      
    public static void initData() throws Exception {  
        //DriverManager.registerDriver(new Mys); 
    	// 驱动程序名
        String driver = "com.mysql.jdbc.Driver";
        Connection conn = null;  
        try {  
        	// 加载驱动程序
            Class.forName(driver);
            String url = "jdbc:mysql://192.168.0.200:3306/caiji";  
            String username = "root";  
            String password = "123456";  
            conn = DriverManager.getConnection(url, username, password);  
            conn.setAutoCommit(false);  
              
           // String sql="select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%')";
           
            //testsql1(sql,conn);
            List<SqlEntity> list=sqlentity();
            for (SqlEntity sqle : list) {
            	System.out.println(sqle.getSql());
            	//testsql1(sqle,conn);
			}
              
            conn.commit();  
        } catch (Exception e) {  
            e.printStackTrace();  
            conn.rollback();  
              
        } finally {  
            conn.close();  
        }  
    }
    
    private static void  testsql1(SqlEntity sqle,Connection conn) {
    	PreparedStatement pstmt;
    	
        
        try {
			pstmt = (PreparedStatement)conn.prepareStatement(sqle.getSql());
			System.out.println("============================");
			long begin = System.currentTimeMillis();
			ResultSet rs = pstmt.executeQuery();
	        int col = rs.getMetaData().getColumnCount();
	        
	        
	        while (rs.next()) {
	            for (int i = 1; i <= col; i++) {
	                System.out.print(rs.getString(i) + "\t");
	                if ((i == 2) && (rs.getString(i).length() < 8)) {
	                    System.out.print("\t");
	                }
	             }
	            System.out.println("");
	        }
	        System.out.println(sqle.getDescription());
	        long end = System.currentTimeMillis();
	        pstmt.close();
	        System.out.println("============================"+(end-begin));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
    }
    
    private static List<SqlEntity> sqlentity(){
    	List<SqlEntity> list = new ArrayList<SqlEntity>();
    	SqlEntity e1 = new SqlEntity();
    	e1.setDescription("第一组 内表大 外表小 in");
    	e1.setSql("select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%')");
    	
    	SqlEntity e2 = new SqlEntity();
    	e2.setDescription("第一组 内表大 外表小 in[小表增加group]");
    	e2.setSql("select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%' group by uuid)");
    	
    	SqlEntity e3 = new SqlEntity();
    	e3.setDescription("第一组 内表大 外表小  采用过滤 exists");
    	e3.setSql("select count(*) from base_customer_sign  \n" + 
    			"where exists (select 1 from base_customer where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)");
    	
    	SqlEntity e31 = new SqlEntity();
    	e31.setDescription("第一组 内表大 外表小  嵌套循环执行");
    	e31.setSql("select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where customerId like 'user%')");
    	
    	SqlEntity e311 = new SqlEntity();
    	e311.setDescription("第一组 内表大外表小exist");
    	e311.setSql("select count(*) from base_customer_sign  \n" + 
    			"where exists (select 1 from base_customer where base_customer.customerId like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid) ");
    	
    	SqlEntity e3111 = new SqlEntity();
    	e3111.setDescription("第二组 内表小 外表大1");
    	e3111.setSql("select count(*) from base_customer where uuid in (select signCustomerUuid from base_customer_sign where trueName like 'user%')");
    	
    	SqlEntity e31111 = new SqlEntity();
    	e31111.setDescription("第二组 内表小 外表大2");
    	e31111.setSql("select count(*) from base_customer  \n" + 
    			"where exists (select 1 from base_customer_sign where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)");
    	
    	
    	SqlEntity e311111 = new SqlEntity();
    	e311111.setDescription("第二组 内表小 外表大3");
    	e311111.setSql("select count(*) from base_customer where uuid in (select signCustomerUuid from base_customer_sign where uuid>1 and signSequenceCount <= 1)");
    	
    	SqlEntity e3111111 = new SqlEntity();
    	e3111111.setDescription("第二组 内表小 外表大4[没有索引]");
    	e3111111.setSql("select count(*) from base_customer  \n" + 
    			"where exists (select 1 from base_customer_sign where base_customer_sign.uuid>1 and base_customer.uuid = base_customer_sign.signCustomerUuid)");
    	
    	
    	
    	
    	list.add(e1);
    	list.add(e2);
    	list.add(e3);
    	list.add(e31);
    	list.add(e311);
    	list.add(e3111);
    	list.add(e31111);
    	list.add(e311111);
    	list.add(e3111111);
 
    	return list;
    }
    
    

}
